﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;
using Excel = Microsoft.Office.Interop.Excel;

namespace UIWPF
{
    public static class ExcelUtility
    {
        public static string ExportToExcel(DataSet ds, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            string result = "";
            try
            {
                excel.Visible = false;
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                Workbook book = excel.Workbooks.Add(true);
                Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)
                    book.Worksheets.Add(
                        Missing.Value, Missing.Value, ds.Tables.Count - 1, 
                        Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet
                    );

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    System.Data.DataTable table = ds.Tables[i];
                    Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
                    int rowIndex = 1;
                    int colIndex = 0;

                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        sheet.Cells[1, colIndex] = col.ColumnName;
                    }
                    foreach (DataRow row in table.Rows)
                    {
                        rowIndex++;
                        colIndex = 0;
                        foreach (DataColumn col in table.Columns)
                        {
                            colIndex++;
                            String typeName = row[col.ColumnName].GetType().ToString();
                            sheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                    sheet.Name = ds.Tables[i].TableName;
                }

                // book.Save();
                book.SaveAs(filePath, Missing.Value, Missing.Value, 
                            Missing.Value, Missing.Value, Missing.Value,
                            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
                            Missing.Value, Missing.Value,
                            Missing.Value, Missing.Value, Missing.Value);
                // excel.Save(strExcelFileName);
                result = "success";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                excel.Quit();
                excel = null;
                GC.Collect();
            }
            return result;
        }

        public static DataTable InitDataTable(string name, List<string> columnNames, List<string> rowNames)
        {
            DataTable dt = new DataTable(name);
            dt.Columns.Add("", Type.GetType("System.String"));
            foreach (var cn in columnNames)
            {
                dt.Columns.Add(cn, Type.GetType("System.String"));
            }
            foreach (var rn in rowNames)
            {
                DataRow nRow = dt.NewRow();
                nRow[0] = rn;
                dt.Rows.Add(nRow);
            }
            return dt;
        }

        public static void EditDataTable(ref DataTable dt, int column, int row, string content)
        {
            DataRow dr = dt.Rows[row];
            dr.BeginEdit();
            dr[column] = content;
            dr.EndEdit();
        }
    }
}
